package com.tsfyun.common.base.util;

import cn.hutool.core.collection.CollUtil;
import com.tsfyun.common.base.exception.ClientException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.lang.Nullable;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.IntStream;

/**
 * =
 * Excel工具类
 */
public class ExcelUtils {

    /**
     * =
     * 解析导入数据
     *
     * @param file
     * @param minCell
     * @return
     */
    public static List analysisImport(MultipartFile file, int minCell) {
        if (file == null) {
            throw new ClientException("请选择您要导入的Excel文件");
        }
        List list = new ArrayList();
        InputStream inp = null;
        Workbook wb = null;
        try {
            inp = file.getInputStream();
            wb = Workbook.getWorkbook(inp);
            //取第一个sheet
            Sheet sheet = wb.getSheet(0);
            int totalRows = sheet.getRows();
            if (totalRows > 1) {
                int cellsSize = sheet.getRow(0).length;
                if (cellsSize < minCell) {
                    throw new ClientException("导入数据列不正确请下载模板核对");
                }
                for (int i = 1; i < totalRows; i++) {
                    String[] rows = new String[cellsSize];
                    Cell[] cells = sheet.getRow(i);
                    for (int j = 0; j < cells.length; j++) {
                        if (j < cellsSize) {
                            String content = StringUtils.removeSpecialSymbol(cells[j].getContents());
                            if (StringUtils.isNotEmpty(content)) {
                                content = new String(content.getBytes("utf-8"));
                            }
                            rows[j] = content;
                        }
                    }
                    list.add(rows);
                }
            }
        } catch (ClientException ce) {
            throw ce;
        } catch (Exception e) {
            throw new ClientException("支持.xls文件导入");
        } finally {
            try {
                if (inp != null) {
                    inp.close();
                }
                if (wb != null) {
                    wb.close();
                }
            } catch (Exception e) {
            }
        }
        return list;
    }

    /**
     * 生成标准workbook，防止xlsx不兼容，暂生成xls
     * @param sheetName
     * @param titles
     * @param dataList
     * @return
     */
    public static HSSFWorkbook standard(@Nullable String sheetName, List<String> titles, List<LinkedHashMap<String,Object>> dataList) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet;
        if(StringUtils.isNotEmpty(sheetName)) {
            sheet = workbook.createSheet(sheetName);
        } else {
            sheet = workbook.createSheet();
        }
        //创建标题行列
        HSSFRow titleRow = sheet.createRow(0);
        IntStream.range(0, titles.size()).forEachOrdered(i -> {
            HSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(titles.get(i));
        });
        if(CollUtil.isEmpty(dataList)) {
            return workbook;
        }
        //创建值行列
        IntStream.range(0,dataList.size()).forEachOrdered(i->{
            HSSFRow valRow = sheet.createRow(i + 1);
            LinkedHashMap<String,Object> dataMap = dataList.get(i);
            int cellIndex = 0;
            for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
                HSSFCell valCell = valRow.createCell(cellIndex);
                valCell.setCellValue(StringUtils.null2EmptyWithTrim(entry.getValue()));
                cellIndex++;
            }
        });
        return workbook;
    }

}
